package com.zhouzhou.fastjava.query;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.zhouzhou.fastjava.query.dialect.Dialect;
import com.zhouzhou.fastjava.util.CollectionKit;
import freemarker.template.Template;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateUtils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.StringWriter;
import java.io.Writer;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public abstract class AbstractQueryExecutor implements QueryExecutor {

    protected final Logger logger = LoggerFactory.getLogger(this.getClass());

    private final QueryConfiguration configuration;

    private final ColumnMapping columnMapping;

    private final Dialect dialect;

    public AbstractQueryExecutor(QueryConfiguration configuration) {
        super();
        this.configuration = configuration;
        this.columnMapping = configuration.getColumnMapping();
        this.dialect = configuration.getDialect();
    }

    class QueryObject {
        String selectSql;
        Object[] sqlParams;
        ModuleQueryStatement mqStatement;

        public String getSelectSql() {
            return selectSql;
        }

        public void setSelectSql(String selectSql) {
            this.selectSql = selectSql;
        }

        public Object[] getSqlParams() {
            return sqlParams;
        }

        public void setSqlParams(Object[] sqlParams) {
            this.sqlParams = sqlParams;
        }

        public ModuleQueryStatement getMqStatement() {
            return mqStatement;
        }

        public void setMqStatement(ModuleQueryStatement mqStatement) {
            this.mqStatement = mqStatement;
        }
    }

    @Override
    public QueryResult execute(String module, QueryArgs args) throws Exception {
        QueryObject queryObject = buildQueryObject(module, args);
        String queryListSQL = dialect.getQueryListSQL(module, columnMapping, queryObject.getSelectSql(), queryObject.getMqStatement(), args, args.getSortors());
        List<Map<String, Object>> list = getList(queryListSQL, queryObject.getSqlParams());
        processResultList(queryObject.getMqStatement(), list);
        Long total = getTotal(queryObject.getSelectSql(), queryObject.getSqlParams());
        return new QueryResult(list, total);
    }

    @Override
    public List<Map<String, Object>> queryForListByModule(String module, QueryArgs args) throws Exception {
        QueryObject queryObject = buildQueryObject(module, args);
        if (configuration.isShowSql()) {
            logger.info("SQL: {}", queryObject.getSelectSql().replaceAll("\\s+", " "));
            logger.info("Parameters: {}", Arrays.stream(queryObject.getSqlParams()).map(r -> r.getClass().getSimpleName() + ":" + r).collect(Collectors.toList()));
        }
        return queryForList(queryObject.getSelectSql(), queryObject.getSqlParams());
    }

    private QueryObject buildQueryObject(String module, QueryArgs args) throws Exception {
        QueryObject queryObject = new QueryObject();
        ModuleQueryStatement mqStatement = configuration.getModuleQueryStatement(module);
        queryObject.setMqStatement(mqStatement);
        QuerySetting querySetting = mqStatement.getQuerySetting();
        Map<String, QueryField> queryFields = querySetting.getFields();

        Template template = new Template(module, mqStatement.getSql(), configuration.getFreemarkerConfiguration());
        // do static
        {
            String templateContent = template.toString();
            Pattern pattern = Pattern.compile(" ([a-zA-Z_0-9]+)\\?\\?");
            Matcher matcher = pattern.matcher(templateContent);
            while (matcher.find()) {
                String group = matcher.group(1);
                args.doStatic(group);
            }
        }
        Map<String, Object> staticFilters = Maps.newHashMap();
        if (args.getStaticFilters() != null) {
            for (Entry<String, String> filter : args.getStaticFilters().entrySet()) {
                QueryField queryField = getQueryField(queryFields, filter.getKey());
                if (StringUtils.isNotBlank(filter.getValue())) {
                    Object targetValue = getTargetValue(queryField, filter.getValue());

                    staticFilters.put(filter.getKey(), targetValue);
                    staticFilters.put(queryField.getName(), targetValue);
                }
            }
        }

        Writer out = new StringWriter();
        template.process(staticFilters, out);
        String templateSql = out.toString();

        List<Object> params = Lists.newArrayList();
        // handle static
        {
            List<String> placeHolders = getPlaceHolders(templateSql);
            for (String placeHolder : placeHolders) {
                String targetKey = placeHolder;
                boolean isLike = placeHolder.startsWith("LIKE");
                if (isLike) {
                    targetKey = placeHolder.substring("LIKE".length());
                }
                if (!staticFilters.containsKey(targetKey)) {
                    continue;
                }
                Object value = staticFilters.get(targetKey);
                QueryField queryField = getQueryField(queryFields, targetKey);
                if (queryField.isArray()) {
                    if (isLike) {
                        throw new QueryException("array filter can't be start with LIKE");
                    }
                    List<String> holders = ((List<?>) value).stream().map(r -> configuration.isForceCaseIgnore() ? "UPPER(?)" : "?").collect(Collectors.toList());
                    params.addAll(((List<?>) value));
                    templateSql = templateSql.replace("@{" + placeHolder + "}", "(" + StringUtils.join(holders, ", ") + ")");
                } else {
                    if (isLike) {
                        templateSql = templateSql.replace("@{" + placeHolder + "}", configuration.isForceCaseIgnore() ? "LIKE CONCAT('%', UPPER(?), '%')" : "LIKE CONCAT('%', ?, '%')");
                    } else {
                        templateSql = templateSql.replace("@{" + placeHolder + "}", "?");
                    }
                    params.add(value);
                }
            }
        }
        StringBuilder sqlBuilder = appendFilterProperties(args, queryFields, templateSql, params);

        String selectSQL = cleanSql(sqlBuilder.toString());
        Object[] sqlParams = params.toArray(new Object[params.size()]);
        queryObject.setSelectSql(selectSQL);
        queryObject.setSqlParams(sqlParams);
        return queryObject;
    }

    protected Long getTotal(String selectSQL, Object[] sqlParams) {
        return this.queryForObject("SELECT COUNT(1) FROM (" + selectSQL + ") RR", Long.class, sqlParams);
    }

    protected StringBuilder appendFilterProperties(QueryArgs args, Map<String, QueryField> queryFields, String templateSql, List<Object> params) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM (").append(templateSql).append(") R");

        if (args.getFilters() != null && args.getFilters().size() > 0) {
            sqlBuilder.append(" WHERE ");
            for (QueryFilter filter : args.getFilters()) {
                String filterValue = filter.getValue();
                if (StringUtils.isBlank(filterValue)) {
                    continue;
                }
                String filterName = filter.getName();
                QueryField queryField = getQueryField(queryFields, filterName);
                Object value = getTargetValue(queryField, filterValue);
                if (queryField.isArray()) {
                    throw new QueryException("array mut be static filter");
                } else {
                    sqlBuilder.append(" AND ").append(getQueryFieldSQL(queryField));
                    params.add(value);
                }
            }
        }
        return sqlBuilder;
    }

    protected String getQueryFieldSQL(QueryField queryField) {

        String columnName = columnMapping.mapping(queryField.getName());
        QueryFieldType type = queryField.getType();
        return getQueryFieldSQL(columnName, type);
    }

    private String getQueryFieldSQL(String columnName, QueryFieldType type) {
        switch (type) {
            case STRING_LIKE:
                if (configuration.isForceCaseIgnore()) {
                    return "UPPER(" + columnName + ") LIKE CONCAT('%', UPPER(?), '%')";
                } else {
                    return columnName + " LIKE CONCAT('%', ?, '%')";
                }
            case STRING_EQ:
            case NUMBER_EQ:
            case DATE_EQ:
            case BOOLEAN_EQ:
                return String.format("%s = ?", columnName);
            case STRING_IEQ:
                if (configuration.isForceCaseIgnore()) {
                    return String.format("UPPER(%s) = UPPER(?)", columnName);
                } else {
                    return String.format("%s = ?", columnName);
                }
            case DATE_GE:
            case NUMBER_GE:
                return String.format("%s >= ?", columnName);
            case DATE_LE:
            case NUMBER_LE:
                return String.format("%s <= ?", columnName);
            case BOOLEAN_NE:
                return String.format("%s <> ?", columnName);
        }
        return "NULL";
    }

    protected QueryField getQueryField(Map<String, QueryField> queryFields, String key) {
        if (queryFields.containsKey(key)) {
            QueryField queryField = queryFields.get(key);
            queryField.setName(key);
            return queryField;
        }
        Map<String, QueryField> wildcardKeyQueryFields = configuration.getWildcardKeyQueryFields();
        for (Entry<String, QueryField> entry : wildcardKeyQueryFields.entrySet()) {
            Matcher matcher = Pattern.compile(entry.getKey()).matcher(key);
            if (matcher.find()) {
                QueryField queryField = entry.getValue();
                return queryField.ofNew(matcher.group(1));
            }
        }
        return new QueryField(key);
    }

    protected String cleanSql(String sql) {
        String newSql = sql.replaceAll("(?i)(where)\\s+and", "$1");
        newSql = newSql.replaceAll("(?i)(where)\\s+\\)", ")");
        return newSql;
    }

    protected Object getTargetValue(QueryField queryField, String filterValue) {

        QueryFieldType type = queryField.getType();
        if (queryField.isArray()) {
            String[] values = StringUtils.split(filterValue, ", \n \t");
            List<Object> targetValues = Lists.newArrayList();
            for (String item : values) {
                targetValues.add(getSingleTargetValue(type, item));
            }
            return targetValues;
        } else {
            return getSingleTargetValue(type, filterValue);
        }
    }

    private final String[] DATE_PATTERNS = {
            "yyyy-MM-dd'T'HH:mm:ss",
            "yyyy-MM-dd",
            "yyyy-MM-dd HH:mm:ss"
    };

    private Object getSingleTargetValue(QueryFieldType typeOperation, String value) {
        switch (typeOperation) {
            case DATE_EQ:
            case DATE_LE:
            case DATE_GE:
                Date queryDate = null;
                if (NumberUtils.isNumber(value)) {
                    queryDate = new Date(Long.valueOf(value));
                } else {
                    for (String pattern : DATE_PATTERNS) {
                        try {
                            queryDate = new SimpleDateFormat(pattern).parse(value);
                            break;
                        } catch (ParseException e) {
                            // ignore
                        }
                    }
                    if (queryDate == null) {
                        throw new QueryException(value + "无法解析为日期");
                    }
                }
                Date targetDate = DateUtils.truncate(queryDate, Calendar.DAY_OF_MONTH);
                if (typeOperation == QueryFieldType.DATE_LE) {
                    return DateUtils.addDays(targetDate, 1);
                }
                return targetDate;
            case BOOLEAN_EQ:
            case BOOLEAN_NE:
                return BooleanUtils.toBoolean(value);
            case NUMBER_EQ:
            case NUMBER_GE:
            case NUMBER_LE:
                return NumberUtils.toInt(value);
            case STRING_IEQ:
            case STRING_LIKE:
            case STRING_EQ:
            default:
                return value;
        }
    }

    private void processResultList(ModuleQueryStatement mqStatement, List<Map<String, Object>> list) throws Exception {

        if (CollectionKit.isEmpty(list)) {
            return;
        }
        LabelSetting labelSetting = mqStatement.getLabelSetting();
        if (labelSetting != null) {
            Map<String, LabelType> labelTypes = labelSetting.getLabelTypes();
            if (labelTypes != null && labelTypes.size() > 0) {
                for (Map<String, Object> row : list) {
                    Set<String> labels = row.keySet();
                    for (String label : labels) {
                        if (!labelTypes.containsKey(label)) {
                            return;
                        }
                        LabelType labelType = labelTypes.get(label);
                        Object originValue = row.get(label);
                        Object convertedValue = row.get(label);

                        if (labelType == LabelType.BOOLEAN) {
                            convertedValue = configuration.getConversionService().convert(originValue, Boolean.class);
                        }
                        row.put(label, convertedValue);
                    }
                }
            }
        }
    }

    private List<String> getPlaceHolders(String sql) {

        List<String> placeHolders = Lists.newArrayList();
        char prevChar = '_';
        boolean placeStart = false;
        int placeStartIndex = 0;
        for (int i = 0; i < sql.length(); i++) {
            char ch = sql.charAt(i);
            if (ch == '@') {
                placeStartIndex = i;
            } else if (prevChar == '@' && ch == '{') {
                placeStart = true;
            } else if (ch == '}') {
                if (placeStart) {
                    // end
                    placeHolders.add(sql.substring(placeStartIndex + 2, i));
                    placeStart = false;
                }
            }
            prevChar = ch;
        }
        return placeHolders;
    }

    private List<Map<String, Object>> getList(String sql, Object[] sqlParams) {
        return queryForList(sql, sqlParams);
    }

}
